﻿using System.Text.RegularExpressions;

namespace Swimj.DataCore;

public class PagingHelper : IPagingHelper
{
    public Regex RegexColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b",
        RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

    public Regex RegexDistinct = new Regex(@"\ADISTINCT\s",
        RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

    public Regex RegexOrderBy =
        new Regex(
            @"\bORDER\s+BY\s+(?!.*?(?:\)|\s+)AS\s)(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*",
            RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

    public Regex SimpleRegexOrderBy = new Regex(@"\bORDER\s+BY\s+", RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

    public static IPagingHelper Instance { get; private set; }

    static PagingHelper()
    {
        Instance = new PagingHelper();
    }

    /// <summary>
    ///     Splits the given <paramref name="sql" /> into <paramref name="parts" />;
    /// </summary>
    /// <param name="sql">The SQL to split.</param>
    /// <param name="parts">The SQL parts.</param>
    /// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
    public bool SplitSql(string sql, out SqlParts parts)
    {
        parts.Sql = sql;
        parts.SqlSelectRemoved = null;
        parts.SqlCount = null;
        parts.SqlOrderBy = null;

        // Extract the columns from "SELECT <whatever> FROM"
        var m = RegexColumns.Match(sql);
        if (!m.Success)
            return false;

        // Save column list and replace with COUNT(*)
        var g = m.Groups[1];
        parts.SqlSelectRemoved = sql.Substring(g.Index);

        if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
            parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
        else
            parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);

        // Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
        m = SimpleRegexOrderBy.Match(parts.SqlCount);
        if (m.Success)
        {
            g = m.Groups[0];
            parts.SqlOrderBy = g + parts.SqlCount.Substring(g.Index + g.Length);
            parts.SqlCount = parts.SqlCount.Substring(0, g.Index);
        }

        return true;
    }
}